package com.qf.mapper;
import com.qf.entity.StudentVO;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface StudentMapper {
    // 查询所有学生信息以及关联的班级信息
    @Select("  SELECT * FROM stu s LEFT JOIN classroom c ON s.class_id = c.cid")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "sname", property = "sname"),
            @Result(column = "age", property = "age"),
            @Result(column = "score", property = "score"),
            @Result(column = "birthday", property = "birthday"),
            @Result(column = "class_id", property = "classId"),
            @Result(column = "cid", property = "classroom.cid"),
            @Result(column = "cnum", property = "classroom.cnum"),
            @Result(column = "seatNum", property = "classroom.seatNum")
    })
    List<StudentVO> findAllStudentAndClassroom();
    // 根据id查询1个学生信息以及关联的所有学科信息
    @Select("SELECT * FROM stu WHERE id = #{id}")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "sname", property = "sname"),
            @Result(column = "age", property = "age"),
            @Result(column = "score", property = "score"),
            @Result(column = "birthday", property = "birthday"),
            @Result(column = "class_id", property = "classId"),
            @Result(column = "id", property = "subjectList",
                    many = @Many(select = "com.qf.mapper.SubjectMapper.findSubjectsByStuId"))
    })
    StudentVO findStudentWithSubjectsById(Integer id);
    // 根据id查询1个学生信息以及关联的所有学科信息以及每个学科关联的老师信息
    @Select("SELECT * FROM stu WHERE id = #{sid}")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "sname", property = "sname"),
            @Result(column = "age", property = "age"),
            @Result(column = "score", property = "score"),
            @Result(column = "birthday", property = "birthday"),
            @Result(column = "class_id", property = "classId"),
            @Result(column = "id", property = "subjectList",
                    many = @Many(select = "com.qf.mapper.SubjectMapper.findSubjectsWithTeachersByStuId")),
    })
    StudentVO findStudentAndAllSubjectAndTeacherByStuid(int sid);

}
